# C conc, 1.21 Si 1.89 Mn2.04 P0.12 S0.06 Cr4.24 Mo1.27 Ni4.38 Cu0.49 V0.55 Nb0.24 Al0.32
# C    k, 1.06 Si 1.04 Mn1.03 P1.06 S1.06 Cr0.99 Mo0.99 Ni1.01 Cu0.95 V1.04 Nb1.10 Al1.03
from datetime import date
import getpath
import os
import logging
import re
import config_json
import copy
import numpy
import time
from PySide6.QtCore import QDateTime, QTime
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
import sqlalchemy
from sqlalchemy import or_, and_, func


class MyDb():
    def __init__(self):
        self.inited=self.init_db()

    def init_db(self):
        if config_json.setting['database'] == None:
            return False
        if not os.path.exists(config_json.setting['database']):
            return False
        logging.info(config_json.setting['database'])
        try:
            self.Base = automap_base()
            filedb = config_json.setting['database']
            print(filedb)
            engine = create_engine('sqlite:///'+filedb, echo=True)
            # reflect the tables
            self.Base.prepare(autoload_with=engine)
            Session = sessionmaker(bind=engine)
            self.session = Session()
            print(dir(self.Base.classes))
            return True
        except sqlalchemy.exc.OperationalError as e:
            print(e)
            return False
        except sqlalchemy.exc.DatabaseError as e:
            print(e)
            return False

    def getData(self, db_str, de_str):
        cmd = "select * from result where sampleid between '" + \
            db_str + "' and '" + de_str + "' "
        cursor = self.session.execute(cmd)  # 执行sql语句
        raw = cursor.fetchall()  # 返回结果行 或使用 #raw = cursor.fetchall()
        for one in raw:
            print(one)

    def getDataObjects(self, db_str, de_str):
        task_filter = {
            and_(
                self.Base.classes.Result.SampleId > db_str,
                self.Base.classes.Result.SampleId < de_str
            )
        }
        contacts = self.session.query(
            self.Base.classes.Result).filter(*task_filter).all()
        return contacts
    def getDataIds(self,ids):
        task_filter = {
            self.Base.classes.Result.SampleId.in_(ids)
        }
        contacts = self.session.query(
            self.Base.classes.Result).filter(*task_filter).all()
        return contacts
    def getDataDays(self, v):
        de = QDateTime.currentDateTime()
        de.setTime(QTime(23, 59, 59))
        de_str = de.toString("yyyy-MM-dd hh:mm:ss")
        db = de.addDays(-v)
        db.setTime(QTime(0, 0, 0))
        db_str = db.toString("yyyy-MM-dd hh:mm:ss")
        objs = self.getDataObjects(db_str, de_str)
        return objs

    def test1(self):
        db = QDateTime.currentDateTime()
        db.setTime(QTime(0, 0, 0))
        db_str = db.toString("yyyy-MM-dd hh:mm:ss")
        de = QDateTime.currentDateTime()
        de.setTime(QTime(23, 59, 59))
        de_str = de.toString("yyyy-MM-dd hh:mm:ss")
        print(self.getData(db_str, de_str))


if __name__ == "__main__":
    config_json.read_setting()
    file = os.path.join(getpath.getpath(), "data.db")
    config_json.setting['database'] = file
    db = MyDb()
    # db.getData()
    print(db.getDataDays(200))
